/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * RecTherapy.java
 *
 * Created on Dec 5, 2011, 10:30:35 PM
 */

package comp4081desktopapp;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
/**
 *
 * @author Admin
 */
public class RecTherapy extends javax.swing.JPanel {
    int patientID = 16, cancerID = 11;
    private ArrayList<Integer> biomarkerID = new ArrayList<Integer>();
    private ArrayList<Integer> therapyID1 = new ArrayList<Integer>();
    private ArrayList<Integer> therapyID2 = new ArrayList<Integer>();
    
    /** Creates new form RecTherapy */
    public RecTherapy() {
        initComponents();
        fillGUI();
    }
    
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jScrollPane1 = new javax.swing.JScrollPane();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jTextField1 = new javax.swing.JTextField();
        jButton1 = new javax.swing.JButton();

        setName("Form"); // NOI18N

        jScrollPane1.setName("pane"); // NOI18N

        org.jdesktop.application.ResourceMap resourceMap = org.jdesktop.application.Application.getInstance(comp4081desktopapp.Comp4081desktopApp.class).getContext().getResourceMap(RecTherapy.class);
        jLabel1.setFont(resourceMap.getFont("title.font")); // NOI18N
        jLabel1.setText(resourceMap.getString("title.text")); // NOI18N
        jLabel1.setName("title"); // NOI18N

        jLabel2.setText(resourceMap.getString("tidlabel.text")); // NOI18N
        jLabel2.setName("tidlabel"); // NOI18N

        jTextField1.setText(resourceMap.getString("tidfield.text")); // NOI18N
        jTextField1.setToolTipText(resourceMap.getString("tidfield.toolTipText")); // NOI18N
        jTextField1.setName("tidfield"); // NOI18N

        jButton1.setLabel(resourceMap.getString("button.label")); // NOI18N
        jButton1.setName("button"); // NOI18N
        jButton1.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jButton1MouseClicked(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane1, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, 400, Short.MAX_VALUE)
            .addGroup(layout.createSequentialGroup()
                .addGap(80, 80, 80)
                .addComponent(jLabel2)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, 56, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jButton1)
                .addContainerGap(121, Short.MAX_VALUE))
            .addGroup(layout.createSequentialGroup()
                .addGap(126, 126, 126)
                .addComponent(jLabel1)
                .addContainerGap(126, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(6, 6, 6)
                .addComponent(jLabel1)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 207, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 16, Short.MAX_VALUE)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel2)
                    .addComponent(jButton1))
                .addGap(20, 20, 20))
        );
    }// </editor-fold>//GEN-END:initComponents

private void fillGUI(){
    try { 
    //this try/catch establishes connection and passes sql query, and the idea is to print the retun
            String connectURL = "jdbc:mysql://141.225.166.202:3306/test"; // test is the name of the database            
            Connection connection = null;// declare a connection by using Connection interface             
            Statement statement = null;// declare object of Statement interface that uses for executing sql statements.            
            //ResultSet rs = null;// declares results for output data from the table.
            //int updateQuery = 0;
            String status = "";
            status = "connecting...\n";				
            System.out.println(status);
            Class.forName("com.mysql.jdbc.Driver").newInstance();// Load JBBC driver "com.mysql.jdbc.Driver".            
            connection = DriverManager.getConnection(connectURL, "root", "cancer11");// Create a connection by using getConnection()
            status = status + "...connected\n";				
            System.out.println(status);
            /* createStatement() is used for create statement object that is used for sending sql statements
to the specified database. */

            status = status + "creating statement\n";			
            System.out.println(status);

            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

            status = status + "created statement\n";				
            System.out.println(status);

            status = status + "sending query\n";				
            System.out.println(status);

//query therapy id from cancer/therapy
            ResultSet rsTHID1 = statement.executeQuery("SELECT THERAPY_ID from CANCER_TYPE_THERAPY where CANCER_TYPE_ID = '" + cancerID + "'");
            rsTHID1.first();
            therapyID1.clear();
            therapyID1.add(rsTHID1.getInt(1));
            while(rsTHID1.next()){
                    therapyID1.add(rsTHID1.getInt(1));}
            rsTHID1.close();
            System.out.println(therapyID1.toString());
//query therapy id from biomarker/therapy
            therapyID2.clear();
            ResultSet rsTHID2 = statement.executeQuery("SELECT THERAPY_ID from THERAPY_BIOMARKER where BIOMARKER_ID = '" + biomarkerID.get(0) + "'");
            for(int j=0; j < biomarkerID.size();j++){
            rsTHID2 = statement.executeQuery("SELECT THERAPY_ID from THERAPY_BIOMARKER where BIOMARKER_ID = '" + biomarkerID.get(j) + "'");
            rsTHID2.first();

            therapyID2.add(rsTHID2.getInt(1));
            while(rsTHID2.next()){
                    therapyID2.add(rsTHID2.getInt(1));}
            rsTHID2.close();
            }
            System.out.println(therapyID2.toString());          
    }
      catch (SQLException a) {

                    System.out.println(a.getMessage());
     } catch (InstantiationException b) {

                    System.out.println(b.getMessage());
     } catch (IllegalAccessException c) {

                    System.out.println(c.getMessage());
     } catch (ClassNotFoundException d) {

                    System.out.println(d.getMessage());
                    }
    
}    
private void jButton1MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jButton1MouseClicked
// TODO add your handling code here:
}//GEN-LAST:event_jButton1MouseClicked

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButton1;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTextField jTextField1;
    // End of variables declaration//GEN-END:variables
}
